package gbench.sandbox.driver;

import gbench.common.matlib.data.DataReader.DFrame;
import gbench.common.matlib.rdd.BasePair;
import gbench.common.tree.LittleTree.IRecord;
import gbench.common.tree.LittleTree.Jdbc;
import gbench.common.tree.LittleTree.Jdbc.JdbcConfig;
import gbench.common.tree.LittleTree.Term;
import gbench.commonApp.jdbc.IJdbcApp;
import gbench.commonApp.jdbc.ISqlDatabase;
import gbench.mydata.driver.MyDataResultSet;
import gbench.mydata.driver.core.codecs.Codecs;
import gbench.mydata.driver.core.data.DataFile;

import static gbench.common.matlib.MatlibCanvas.*;
import static gbench.common.tree.LittleTree.IRecord.*;

import org.junit.jupiter.api.Test;

public class JunitMydata {

    @Test
    public void bar() {
        final var x = new DataFile("C:\\Users\\gbench\\Desktop\\tmp\\book.xlsx");

        println(x.get(0));
        println(new Codecs().encode("C:/Users/gbench/Desktop/tmp/book.xlsx"));
    }

    @JdbcConfig(url = "jdbc:mydata:excel://localhost/0?C%3A%2FUsers%2Fgbench%2FDesktop%2Ftmp%2Fbook.xlsx", driver = "gbench.mydata.driver.MyDataDriver", user = "root", password = "123456")
    interface MyDatabase extends ISqlDatabase { // 数据接口

    }

    /**
     * 请把端点设置在 <br>
     * gbench.mydata.driver.MyDataResultSet.close 和 <br>
     * gbench.mydata.driver.core.data.DataFile.close <br>
     * 方法上 <br>
     * 查看 rs.close 方法的调用情况。
     */
    @Test
    public void foo() {
        final var db = IJdbcApp.newNspebDBInstance("", MyDatabase.class);
        final var jdbc = db.getProxy().findOne(Jdbc.class);
        jdbc.withTransaction(sess -> {
            // 调用一次 rs.close 方法, 由 readlineS内的next运算子的callback.run(); 发起，
            // 对于 sess.psql2recordS 系列方法, 只要 读到rs的afterLast 就会自动调用close 系列函数，
            // 完成语句stmt,resultset 结果集资源释放
            final var data = sess.sql2u("", DFrame.dfmclc);
            println(data);
        }); // 调用一次rs.close 方法, 由 session.clear() 发起

        jdbc.withConnection(conn -> {
            final var stmt = conn.prepareStatement("");
            final var rs = stmt.executeQuery();
            if (rs.isBeforeFirst()) {
                rs.next();
            }

            println("-----------------------------------------------");
            final var rb = Jdbc.linebuilder(rs); // 列名构建器
            println(rb.get(Jdbc.readlineA(rs))); // 不会改变游标
            println("按照名称读取", rs.getObject("PartA"));
            println("按照列序号读取 3 obj", rs.getObject(3));
            println("按照列序号读取 1 int", rs.getInt(1));
            println("按照列名称读取 Id dbl", rs.getDouble("Id"));
            println("按照列名 读取 Id date", rs.getDate("Id"));
            println("-----------------------------------------------");
            // 不会 调用 rs.close 方法 因为 Jdbc.readlineS(rs) 默认是 Jdbc.readlineS(rs, false)
            println(Jdbc.readlineS(rs).collect(DFrame.dfmclc)); // readlineS 会自动 移动游标，所以 从 第二行 开始;
            println("---------------------操纵游标---------------------");
            println("isAfterLast", rs.isAfterLast());
            println("前进一步", rs.previous(), "rownum", rs.getRow(), "entry", Jdbc.readline(rs));
            println("is isLast", rs.isLast());
            println("前进一步", rs.previous(), "rownum", rs.getRow(), "entry", Jdbc.readline(rs));
            // 不会 调用 rs.close 方法 因为 Jdbc.readlineS(rs) 默认是 Jdbc.readlineS(rs, false)
            println("读出后续所有：\n", Jdbc.readlineS(rs).collect(DFrame.dfmclc));
            println("移动到第一行 absolute 1", rs.absolute(1));
            println("读出第一行", Jdbc.readline(rs));
            println("移动到第一行以前");
            rs.beforeFirst();
            println("读出后续所有行：\n", Jdbc.readlineS(rs).collect(DFrame.dfmclc));
            println("移动到最后一行以后");
            rs.afterLast();
            println("向前移动两步", rs.relative(-2));
            // 不会 调用 rs.close 方法 因为 Jdbc.readlineS(rs, false)
            println("前进一步", rs.previous(), "rownum", rs.getRow(), "entry", Jdbc.readline(rs));
            // 不会 调用 rs.close 方法 因为 Jdbc.readlineS(rs) 默认是 Jdbc.readlineS(rs, false)
            println("读出后续所有行：\n", Jdbc.readlineS(rs).collect(DFrame.dfmclc));

            rs.close(); // 调用依次 rs.close 方法，关闭结果集合
            stmt.close(); // 关闭语句
            return true; // 返回 运算结果，表示计算成功
        });
    }

    /**
     * 自定义 ResultSet 的使用演示
     */
    @Test
    public void rs_demo() {

        try {
            final var rs = XVec.OF(10, i -> REC("id", i + 1, "name", "zhang" + i)).collect(MyDataResultSet.mdrsclc());
            final var dfm = Jdbc.readlineS(rs).collect(DFrame.dfmclc);
            println(dfm);
            println("当前行:", rs.getRow(), "isAfterLast", rs.isAfterLast());
            println("向前移动五步", rs.relative(-5), "当前行(小于等于0为非法值):", rs.getRow(), "当前记录:", Jdbc.readline(rs));
            final var dfm1 = Jdbc.readlineS(rs).collect(DFrame.dfmclc);
            println(dfm1);

            // 增加新元素
            println("增加新元素前", "当前行(小于等于0为非法值):", rs.getRow(), "isAfterLast", rs.isAfterLast(), "当前记录:",
                    Jdbc.readline(rs));
            rs.append(XVec.OF(10, i -> REC("id", i + 1 + 10, "name", "wang" + i)));
            println("增加10个元素", "当前行(小于等于0为非法值):", rs.getRow(), "isAfterLast", rs.isAfterLast(), "当前记录:",
                    Jdbc.readline(rs));
            final var dfm2 = Jdbc.readlineS(rs).collect(DFrame.dfmclc);
            println(dfm2);
            println("重新移动到开始之前");
            rs.reset();
            final var dfm3 = Jdbc.readlineS(rs).collect(DFrame.dfmclc);
            println(dfm3);

            // 删除前面的5个元素
            println("--------------------------------------------------");
            rs.absolute(6); // 启动到第6行
            println("删除前面的5个元素 执行 前", "当前行(小于等于0为非法值):", rs.getRow(), "isAfterLast", rs.isAfterLast(), "当前记录:",
                    Jdbc.readline(rs), "rs.size", rs.size());
            rs.drop(5); // 删除前五个元素
            println("删除前面的5个元素 执行 后", "当前行(小于等于0为非法值):", rs.getRow(), "isAfterLast", rs.isAfterLast(), "当前记录:",
                    Jdbc.readline(rs), "rs.size", rs.size());
            rs.reset(); // 启动到开始之前
            final var dfm4 = Jdbc.readlineS(rs).collect(DFrame.dfmclc);
            println("删除前面的10个元素 执行后的 重置开始游标 获得的 数据情况\n", dfm4);
            println("删除前面的5个元素 执行 且 遍历后", "当前行(小于等于0为非法值):", rs.getRow(), "isAfterLast", rs.isAfterLast(), "当前记录:",
                    Jdbc.readline(rs), "rs.size", rs.size());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 自定义 ResultSet 的使用演示
     */
    @Test
    public void rs_drop0() {

        try {
            final var rs = XVec.OF(10, i -> REC("id", i + 1, "name", "zhang" + i)).collect(MyDataResultSet.mdrsclc());
            final var dfm = Jdbc.readlineS(rs).collect(DFrame.dfmclc);
            println(dfm);
            println("----------------------------");
            rs.absolute(6);
            rs.drop(-3); // 定位6号元素 从尾部删除3个元素
            println("当前行(小于等于0为非法值):", rs.getRow());
            println(rs.getRow(), Jdbc.readline(rs));
            println(Jdbc.readlineS(rs).collect(DFrame.dfmclc));
            println("当前行(小于等于0为非法值):", rs.getRow(),"业已位于 index_of_after_last");
            println("----------------------------");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 自定义 ResultSet 的使用演示
     */
    @Test
    public void rs_drop1() {

        try {
            @SuppressWarnings("unchecked")
            final var params = IRecord.A(
                    BasePair.bp(-1, BasePair.bp(4, 6)), 
                    BasePair.bp(8, BasePair.bp(4, 5)),
                    BasePair.bp(5, BasePair.bp(4, 5)), 
                    BasePair.bp(1, BasePair.bp(2, 8)),
                    BasePair.bp(1, BasePair.bp(1, 8)));

            for (var param : params) {
                final var rs = XVec.OF(10, i -> REC("id", i + 1, "name", "zhang" + i))
                        .collect(MyDataResultSet.mdrsclc());
                final var pos = param._1();
                final var from = param._2()._1();
                final var to = param._2()._2();
                println("----------------------------");
                rs.relocate(pos);
                rs.drop(from, to);
                println("当前行(小于等于0为非法值):", rs.getRow(), "current line:", Jdbc.readline(rs));
                println(Term.FT("定位$0,删除[$1,$2)\n", pos, from, to), Jdbc.readlineS(rs).collect(DFrame.dfmclc));
                rs.reset();
                println(Term.FT("reset\n"), Jdbc.readlineS(rs).collect(DFrame.dfmclc));
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 自定义 ResultSet 的使用演示
     */
    @Test
    public void rs_insert() {

        try {
            final var aa = XVec.OF(10, i -> REC("id", i + 11, "name", "wang" + i)).list();
            @SuppressWarnings("unchecked")
            final var params = IRecord.A( 
                    BasePair.bp(0, aa),
                    BasePair.bp(10, aa)
                );
            
            for (var param : params) {
                final var rs = XVec.OF(10, i -> REC("id", i + 1, "name", "zhang" + i))
                        .collect(MyDataResultSet.mdrsclc());
                final var pos = param._1();
                final var ll = param._2();
                println("----------------------------");
                rs.relocate(pos);
                rs.insert(pos, ll);
                println("当前行(小于等于0为非法值):", rs.getRow(), "current line:", Jdbc.readline(rs));
                println(Term.FT("定位$0,增加 $1 个元素)\n", pos, ll.size()), Jdbc.readlineS(rs).collect(DFrame.dfmclc));
                rs.reset();
                println(Term.FT("reset\n", pos, ll), Jdbc.readlineS(rs).collect(DFrame.dfmclc));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 自定义 ResultSet 的使用演示
     */
    @Test
    public void rs_insert2() {

        try {
            final var aa = XVec.OF(10, i -> REC("id", i + 11, "name", "wang" + i)).list();
            @SuppressWarnings("unchecked")
            final var params = IRecord.A( 
                    BasePair.bp(-1, aa),
                    BasePair.bp(0, aa),
                    BasePair.bp(10, aa)
                );

            for (var param : params) {
                final var rs = XVec.OF(10, i -> REC("id", i + 1, "name", "zhang" + i))
                        .collect(MyDataResultSet.mdrsclc());
                final var pos = param._1();
                final var ll = param._2();
                println("----------------------------");
                rs.relocate(pos);
                rs.insert(ll);
                println("当前行(小于等于0为非法值):", rs.getRow(), "current line:", Jdbc.readline(rs));
                println(Term.FT("定位$0,增加 $1 个元素)\n", pos, ll.size()), Jdbc.readlineS(rs).collect(DFrame.dfmclc));
                rs.reset();
                println(Term.FT("reset\n", pos, ll), Jdbc.readlineS(rs).collect(DFrame.dfmclc));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
